const pool = require("../db/conn");
const countControllers = require("../controllers/countControllers");

exports.selectAuthList = async function() {
    let sql =  "SELECT"+
                "	rc_class.class_id, "+
                "	rc_class.class_name "+
                "FROM "+
                "	rc_class "+
                "WHERE "+
                "	rc_class.class_is_delete = 0 ";
    let conn = await pool.getConnection();
    let [result] = await conn.execute(sql);
    conn.release();
    return result
}

exports.selectList = async function (pageno,pagesize,cname,major,department) {    
    let sql =   " SELECT "+
                " 	rc_class.class_id,  "+
                " 	rc_class.class_grade,  "+
                " 	rc_class.class_name,  "+
                " 	rc_major.major_id,  "+
                " 	rc_major.major_name,  "+
                " 	rc_department.department_id,  "+
                " 	rc_department.department_name "+
                " FROM "+
                " 	rc_class "+
                " 	LEFT JOIN "+
                " 	rc_major "+
                " 	ON  "+
                " 		rc_class.class_major_id = rc_major.major_id "+
                " 	LEFT JOIN "+
                " 	rc_department "+
                " 	ON  "+
                " 		rc_major.major_department_id = rc_department.department_id "+
                " WHERE "+
                " 	rc_class.class_is_delete = 0  ";
    let params = [];
    if(cname!=null&&cname!=""){
        sql += "AND rc_class.class_name like ? "
        params.push( `%${cname}%` );
    }
    if(major!=null&&major!=""){
        sql += "AND rc_major.major_name like ? "
        params.push( `%${major}%` );
    }
    if(department!=null&&department!=""){
        sql += "AND rc_department.department_name like ? "
        params.push( `%${department}%` );
    }

    let data = await countControllers.selectCount( sql,params );
    
    sql += "LIMIT ?, ?";
    let start = ((pageno-1)*pagesize)+"";
    let size = pagesize;
    
    params.push(start,size);

    let conn = await pool.getConnection();
    let [result] = await conn.execute(sql,params);
    conn.release();

    data.pageNo = pageno*1
    data.pageSize = pagesize*1;
    data.list = result;
    return data;
}

exports.selectById = async function (class_id) {
        let sql =  "SELECT"+
                    " 	rc_class.class_id,  "+
                    " 	rc_class.class_major_id,  "+
                    " 	rc_class.class_grade,  "+
                    "	rc_class.class_name, "+
                    "	rc_class.class_is_delete "+
                    "FROM "+
                    "	rc_class "+
                    "WHERE "+
                    "	rc_class.class_id = ? ";

    let conn = await pool.getConnection();
    let [result] = await conn.execute(sql,[class_id]);
    conn.release();
    
    return result[0];
}

exports.selectByNameAndCondition = async function (class_name,class_id,class_is_delete) {
    let sql =  "SELECT"+
                " 	rc_class.class_id,  "+
                " 	rc_class.class_major_id,  "+
                " 	rc_class.class_grade,  "+
                "	rc_class.class_name, "+
                "	rc_class.class_is_delete "+
                "FROM "+
                "	rc_class "+
                "WHERE "+
                "	rc_class.class_name = ? ";

    let params = [class_name];
    if(class_id!=null&&class_id!=""){
        sql += "AND rc_class.class_id != ? ";
        params.push( class_id );
    }
    if(class_is_delete!=null&&class_is_delete!=""){
        sql += "AND rc_class.class_is_delete = ? ";
        params.push( class_is_delete );
    }

    let conn = await pool.getConnection();
    let [result] = await conn.execute(sql,params);
    conn.release();
    
    return result[0];
}

exports.insert = async function (class_major_id,class_grade,class_name,state=0) {
    let sql =  "insert into rc_class (class_major_id,class_grade,class_name,class_is_delete) values (?,?,?,?)";

    let conn = await pool.getConnection();
    let [{changedRows}] = await conn.execute(sql,[class_major_id,class_grade,class_name,state]);
    conn.release();
    
    return changedRows!=0;
}

exports.updateByDid = async function (class_id,class_major_id,class_grade,class_name,state=0) {
    let sql =  "UPDATE rc_class SET class_major_id = ? , class_grade = ? , class_name = ?, class_is_delete = ? WHERE class_id = ?";

    let conn = await pool.getConnection();
    let [{changedRows}] = await conn.execute(sql,[class_major_id,class_grade,class_name,state,class_id]);
    conn.release();
    
    return changedRows!=0;
}

exports.deleteByDid = async function (class_id) {
    let sql =  "UPDATE rc_class SET class_is_delete = 1 WHERE class_id = ?";

    let conn = await pool.getConnection();
    let [{changedRows}] = await conn.execute(sql,[class_id]);
    conn.release();
    
    return changedRows!=0;
}
